import os

import psycopg2
from psycopg2 import OperationalError

"""
执行SQL脚本，创建存储过程，或者执行多个SQL脚本
"""


class ExecuteSQLScripts:
    def __init__(self, dbname, user, password, host='localhost', port=5432):
        """
        初始化数据库连接参数
        :param dbname: 数据库名称
        :param user: 用户名
        :param password: 密码
        :param host: 主机地址（默认localhost）
        :param port: 端口号（默认5432）
        """
        self.conn_params = {
            'dbname': dbname,
            'user': user,
            'password': password,
            'host': host,
            'port': port
        }
        self.connection = None

    def connect(self):
        """建立数据库连接"""
        try:
            self.connection = psycopg2.connect(**self.conn_params)
            print("成功连接到PostgreSQL数据库")
            return True
        except OperationalError as e:
            print(f"连接错误: {e}")
            return False

    def disconnect(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            print("数据库连接已关闭")

    def execute_procedure_script(self, script_path):
        """
        执行存储过程创建脚本
        :param script_path: SQL脚本文件路径
        :return: 执行是否成功
        """
        if not self.connection:
            print("未建立数据库连接")
            return False

        try:
            # 读取SQL脚本文件内容
            with open(script_path, 'r', encoding='utf-8') as file:
                sql_script = file.read()

            # 使用事务执行脚本
            with self.connection.cursor() as cursor:
                cursor.execute(sql_script)
            self.connection.commit()
            print("存储过程创建成功")
            return True

        except Exception as e:
            self.connection.rollback()
            print(f"执行错误: {e}")
            return False


# 使用示例
if __name__ == "__main__":
    # 配置数据库连接参数
    manager = ExecuteSQLScripts(
        dbname="log_dev_db",
        user="log",
        password="1",
        host="localhost",
        port=5432
    )

    base_dir = os.path.dirname(os.path.abspath(__file__))
    print('base_dir = ', base_dir)
    # template_folder = os.path.join(base_dir, '..', 'sql', 'create_gauss_table_info_view_p.sql')
    # sql文件跟本文件放到同一个目录下
    template_folder = os.path.join(base_dir, 'create_gauss_table_info_view_p.sql')
    print('template_folder = ', template_folder)
    if manager.connect():
        # 执行存储过程创建脚本
        manager.execute_procedure_script(template_folder)
        manager.disconnect()
